package DARCOServer.db;

import static DARCOShared.utils.Constants.*;

import java.io.File;
import java.security.KeyPair;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.Vector;

import org.tmatesoft.sqljet.core.SqlJetException;
import org.tmatesoft.sqljet.core.SqlJetTransactionMode;
import org.tmatesoft.sqljet.core.table.ISqlJetCursor;
import org.tmatesoft.sqljet.core.table.ISqlJetTable;
import org.tmatesoft.sqljet.core.table.SqlJetDb;

import DARCOServer.security.SecurityManager;
import DARCOShared.requests.ChangeOwnershipRequest;
import DARCOShared.requests.GetCardFromQRScanRequest;
import DARCOShared.requests.GetCardImageRequest;
import DARCOShared.requests.GetCardInfoRequest;
import DARCOShared.requests.GetCardJourneyRequest;
import DARCOShared.requests.GetCardListByOwnerRequest;
import DARCOShared.requests.GetPositionsOfNearbyUsersRequest;
import DARCOShared.requests.UpdateUserPositionRequest;
import DARCOShared.responses.ChangeOwnershipResponse;
import DARCOShared.responses.GetCardFromQRScanResponse;
import DARCOShared.responses.GetCardImageResponse;
import DARCOShared.responses.GetCardInfoResponse;
import DARCOShared.responses.GetCardJourneyResponse;
import DARCOShared.responses.GetCardListByOwnerResponse;
import DARCOShared.responses.GetPositionsOfNearbyUsersResponse;
import DARCOShared.responses.UpdateUserPositionResponse;
import DARCOShared.utils.Algorithms;
import DARCOShared.utils.MD5;
import DARCOShared.utils.SecurityUtilities;
import DARCOShared.utils.Trade;



public class DatabaseManager {
	private static String DB_FILE = "db.sql";
	private static SqlJetDb db;
	private static File dbFile;

	public static void Initialize(String path, boolean freshStart) {
		DB_FILE = path;
		dbFile = new File(DB_FILE);
		if(freshStart){
			if (dbFile.exists()){
				dbFile.delete();
			}
			openDB();
			createDB();
		}
		else
			openDB();
	}

	public static SqlJetDb getDatabaseInstance(){
		return db;
	}

	private static void openDB(){
		try {
			db = SqlJetDb.open(dbFile, true);
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
	}

	public static void closeDB(){
		try {
			db.close();
		} catch (SqlJetException e) {
			e.printStackTrace();
		}
	}

	private static void createDB(){

		try {

			//setta opzioni DB
			db.getOptions().setAutovacuum(true);
			db.beginTransaction(SqlJetTransactionMode.WRITE);
			try {
				db.getOptions().setUserVersion(1);
			} finally {
				db.commit();
			}

			//CREA DB
			//CREATION of the TABLE ACCOUNT 
			try{
				db.createTable("CREATE TABLE " + TABLE_NAME_ACCOUNTS +" ("
						+ ACCOUNT_ID		+" INTEGER, "
						+ ACCOUNT_EMAIL		+" TEXT PRIMARY KEY, "
						+ ACCOUNT_NAME 		+" TEXT, "
						+ ACCOUNT_SURNAME	+" TEXT, "
						+ ACCOUNT_USERNAME	+" TEXT, "
						+ ACCOUNT_PASSWORD	+" TEXT, "
						+ ACCOUNT_LASTLOC   +" TEXT, "
						+ ACCOUNT_CREATION  +" TEXT, "
						+ ACCOUNT_KEYPAIR  +" BLOB, "
						+ ACCOUNT_AVATAR    +" BLOB" +
						"); ");


				db.createIndex("CREATE INDEX "+ACCOUNT_EMAIL+" ON "+TABLE_NAME_ACCOUNTS+"("+ACCOUNT_EMAIL+")");
				db.createIndex("CREATE INDEX "+ACCOUNT_ID+" ON "+TABLE_NAME_ACCOUNTS+"("+ACCOUNT_ID+")");


				//CREATION of the TABLE CARDS 
				db.createTable("CREATE TABLE " + TABLE_NAME_CARDS +" ("
						+ CARD_SN 		+" TEXT, "
						+ CARD_NAME		+" TEXT, "
						+ CARD_STATS	+" TEXT, "
						+ CARD_SPECIAL	+" TEXT, "
						+ CARD_CATEGORY	+" INTEGER, "
						+ CARD_SUBCATEGORY  +" INTEGER, "
						+ CARD_PREVIEW		+" BLOB, "
						+ CARD_IMAGE		+" BLOB, "
						+"PRIMARY KEY ( " + CARD_SN + ")); ");
				db.createIndex("CREATE INDEX "+CARD_SN+" ON "+TABLE_NAME_CARDS+"("+CARD_SN+")");

				db.createTable("CREATE TABLE " + TABLE_NAME_UNIQUECARDS +" ("
						+ UNIQUECARD_ID		+" TEXT, "
						+ UNIQUECARD_SN 		+" TEXT, "
						+ UNIQUECARD_ACTUALOWNER  +" TEXT, " 
						+ UNIQUECARD_AUTENTICATED	+" INTEGER, " 
						+ UNIQUECARD_FIRSTAUTH	+" TEXT, "
						+ UNIQUECARD_FIRSTOWNER	+" TEXT, "
						+"PRIMARY KEY ( "+UNIQUECARD_ID+" , "+UNIQUECARD_SN+
						")); ");
				db.createIndex("CREATE INDEX "+UNIQUECARD_ID+" ON "+TABLE_NAME_UNIQUECARDS+"("+UNIQUECARD_ID+")");
				db.createIndex("CREATE INDEX "+UNIQUECARD_SN+" ON "+TABLE_NAME_UNIQUECARDS+"("+UNIQUECARD_SN+")");
				db.createIndex("CREATE INDEX "+UNIQUECARD_SN_AND_ID+" ON "+TABLE_NAME_UNIQUECARDS+"("+UNIQUECARD_SN+","+UNIQUECARD_ID+")");
				db.createIndex("CREATE INDEX "+UNIQUECARD_ACTUALOWNER+" ON "+TABLE_NAME_UNIQUECARDS+"("+UNIQUECARD_ACTUALOWNER+")");


				//CREATION of the TABLE CATEGORY 		
				db.createTable("CREATE TABLE " + TABLE_NAME_CATEGORIES +" ("
						+ CATEGORY_ID		+" INTEGER PRIMARY KEY, "
						+ CATEGORY_NAME		+" TEXT, "
						+ CATEGORY_ALLOWSUB	+" INTEGER "+
						"); ");
				db.createIndex("CREATE INDEX "+CATEGORY_ID+" ON "+TABLE_NAME_CATEGORIES+"("+CATEGORY_ID+")");

				db.createTable("CREATE TABLE " + TABLE_NAME_TRADES +" ("
						+ TRADE_ID			+" INTEGER PRIMARY KEY AUTOINCREMENT, " //non server il local id dato che sono sul server
						+ TRADE_CARDID		+" TEXT, "
						+ TRADE_CARDSN		+" TEXT, "
						+ TRADE_NEWOWNER	+" TEXT, "
						+ TRADE_OLDOWNER	+" TEXT, "
						+ TRADE_DATE		+" TEXT, "
						+ TRADE_LATITUDE	+" REAL, "
						+ TRADE_LONGITUDE	+" REAL, "
						+ TRADE_SERVER_SIGNATURE +" TEXT "+
						"); ");
				db.createIndex("CREATE INDEX "+TRADE_CARDSN_AND_CARDID +" ON "+TABLE_NAME_TRADES+"("+TRADE_CARDSN + "," + TRADE_CARDID +")");


				db.createTable("CREATE TABLE " + TABLE_NAME_SECURITY +" (" +
						SECURITY_KEYPAIR + " TEXT PRIMARY KEY); ");
				db.createIndex("CREATE INDEX "+SECURITY_KEYPAIR+" ON "+TABLE_NAME_SECURITY+"("+SECURITY_KEYPAIR+")");


				//il server non ha bisogno della tabella system (server solo per app android)
			}
			finally{
				db.commit();
			}

		} catch (SqlJetException e) {
			e.printStackTrace();
		}
	}

	public static int getMaxAccountID(){
		int max=0;
		try{
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);

			ISqlJetTable accountTable = db.getTable(TABLE_NAME_ACCOUNTS);

			ISqlJetCursor cursor= accountTable.order(ACCOUNT_ID);
			if (!cursor.eof()) {
				do {
					int x=(int) cursor.getInteger(ACCOUNT_ID);
					if(max<x){
						max=x;
					}
				} while(cursor.next());
			}

			return max;
		}catch(SqlJetException e){
			e.printStackTrace();
			return -1;}

	}

	public static void insertNewAccount(int id,String email, String name, String surname, String user, String password,byte[] keypair, byte[] avatar){
		Calendar time = Calendar.getInstance();

		try{
			db.beginTransaction(SqlJetTransactionMode.WRITE);
			ISqlJetTable accountTable = db.getTable(TABLE_NAME_ACCOUNTS);

			accountTable.insert(id, email,name,surname,user,MD5.get(password),null,time.getTime().toString(),keypair, avatar);

			db.commit();
		}catch(SqlJetException ex){ex.printStackTrace();}

	}

	public static KeyPair getAccountKeyPair(String username){
		try{
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			ISqlJetTable table5 = db.getTable(TABLE_NAME_ACCOUNTS);
			ISqlJetCursor cursor5 = table5.lookup(ACCOUNT_EMAIL, username);
			return SecurityUtilities.Bin2KeyPair(cursor5.getBlobAsArray(ACCOUNT_KEYPAIR));
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public static KeyPair getAccountKeyPair(int userID){
		try{
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			ISqlJetTable table5 = db.getTable(TABLE_NAME_ACCOUNTS);
			ISqlJetCursor cursor5 = table5.lookup(ACCOUNT_ID, userID);
			return SecurityUtilities.Bin2KeyPair(cursor5.getBlobAsArray(ACCOUNT_KEYPAIR));
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}
	}

	public static ChangeOwnershipResponse ChangeCardOwnership(ChangeOwnershipRequest req){
		try{
			String ownerID = null;
			db.beginTransaction(SqlJetTransactionMode.WRITE);

			ISqlJetTable table2 = db.getTable(TABLE_NAME_UNIQUECARDS);
			ISqlJetCursor cursor2 = table2.lookup(UNIQUECARD_SN_AND_ID, req.cardSN,req.cardUID);
			ownerID = cursor2.getString(UNIQUECARD_ACTUALOWNER);
			KeyPair ownerKeyPair = getAccountKeyPair(ownerID);

			if(SecurityUtilities.verifySignature((req.cardSN + req.cardUID + req.newOwnerUsername).getBytes(), req.currentOwnerTradeSignature, ownerKeyPair.getPublic())){
				//agiorno il proprietario
				Map<String, Object> m = new HashMap<String, Object>();
				m.put(UNIQUECARD_ACTUALOWNER, req.newOwnerUsername); 
				cursor2.updateByFieldNames(m);

				//Creo la trade
				byte[] tradeSignature = SecurityUtilities.signData((req.cardSN + req.cardUID + ownerID + req.newOwnerUsername).getBytes(), SecurityManager.getPrivateKey());
				ISqlJetTable tradeTable = db.getTable(TABLE_NAME_TRADES);
				Map<String, Object> m2 = new HashMap<String, Object>();
				m2.put(TRADE_CARDID, req.cardUID);
				m2.put(TRADE_CARDSN, req.cardSN);
				m2.put(TRADE_DATE, Calendar.getInstance().getTimeInMillis());
				m2.put(TRADE_OLDOWNER, ownerID);
				m2.put(TRADE_NEWOWNER, req.newOwnerUsername);
				m2.put(TRADE_SERVER_SIGNATURE, tradeSignature);
				if(req.locationPresent){
					m2.put(TRADE_LATITUDE, req.latitude);
					m2.put(TRADE_LONGITUDE, req.longitude);
				}
				else{
					m2.put(TRADE_LATITUDE, -1);
					m2.put(TRADE_LONGITUDE, -1);
				}
				tradeTable.insertByFieldNames(m2);

				db.commit();
				return new ChangeOwnershipResponse(getCardInfo(new GetCardInfoRequest(req.cardSN, req.cardUID)));
			}
			else{
				return new ChangeOwnershipResponse("Bad signature");				
			}
		}catch(Exception e1){
			e1.printStackTrace();
			return new ChangeOwnershipResponse("Database error");
		}

	}


	public static ISqlJetCursor getAccountByEmail(String email){
		try{
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			ISqlJetTable accountTable = db.getTable(TABLE_NAME_ACCOUNTS);
			ISqlJetCursor cursor= accountTable.lookup(ACCOUNT_EMAIL, email);


			return cursor;
		}catch(SqlJetException e){
			e.printStackTrace();
			return null;}

	}

	public static boolean updateCardForAuthentication(String sn, String id,String email){

		try{
			db.beginTransaction(SqlJetTransactionMode.WRITE);
			ISqlJetTable table2 = db.getTable(TABLE_NAME_UNIQUECARDS);
			ISqlJetCursor cursor2 = table2.lookup(UNIQUECARD_SN_AND_ID, sn, id);

			Calendar cal = Calendar.getInstance();
			SimpleDateFormat sdf = new SimpleDateFormat(DATETIME_FORMAT);
			String time = sdf.format(cal.getTime());
			cursor2.update(cursor2.getValue(UNIQUECARD_ID),cursor2.getValue(UNIQUECARD_SN),email,1,time,email);
			db.commit();
			return true;

		}catch(SqlJetException e){
			e.printStackTrace();
			return false;}
	}


	public static GetCardInfoResponse getCardInfo(GetCardInfoRequest req){
		try{
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			ISqlJetTable table = db.getTable(TABLE_NAME_CARDS);
			ISqlJetCursor cursor = table.lookup(CARD_SN, req.CardSN);

			ISqlJetTable table2 = db.getTable(TABLE_NAME_UNIQUECARDS);
			ISqlJetCursor cursor2 = table2.lookup(UNIQUECARD_SN_AND_ID, req.CardSN, req.CardUniqueID);

			ISqlJetTable table3 = db.getTable(TABLE_NAME_CATEGORIES);
			ISqlJetCursor cursor3 = table3.lookup(CATEGORY_ID, cursor.getInteger(CARD_CATEGORY));
			ISqlJetCursor cursor4 = table3.lookup(CATEGORY_ID, cursor.getInteger(CARD_SUBCATEGORY));

			String ownerID = null;
			byte[] ownerPubKey = null;
			if(cursor2.getInteger(UNIQUECARD_AUTENTICATED) == 1){
				try{
					ownerID = cursor2.getString(UNIQUECARD_ACTUALOWNER);
					ISqlJetTable table5 = db.getTable(TABLE_NAME_ACCOUNTS);
					ISqlJetCursor cursor5 = table5.lookup(ACCOUNT_EMAIL, ownerID);
					ownerID = cursor5.getString(ACCOUNT_EMAIL);
					KeyPair ownerKeyPair = SecurityUtilities.Bin2KeyPair(cursor5.getBlobAsArray(ACCOUNT_KEYPAIR));
					ownerPubKey = SecurityUtilities.SerializeToBin(ownerKeyPair.getPublic());
				}catch(Exception e1){
					e1.printStackTrace();
				}
			}

			GetCardInfoResponse resp = new GetCardInfoResponse(
					cursor.getString(CARD_SN),
					cursor2.getString(UNIQUECARD_ID),
					cursor.getString(CARD_NAME),
					cursor.getString(CARD_STATS),
					cursor.getString(CARD_SPECIAL),
					(int)cursor.getInteger(CARD_CATEGORY),
					(int)cursor.getInteger(CARD_SUBCATEGORY),
					cursor.getBlobAsArray(CARD_IMAGE),
					cursor.getBlobAsArray(CARD_PREVIEW),
					cursor3.getString(CATEGORY_NAME),
					(int)cursor3.getInteger(CATEGORY_ID),
					(int)cursor3.getInteger(CATEGORY_ALLOWSUB),
					cursor4.getString(CATEGORY_NAME),
					(int)cursor4.getInteger(CATEGORY_ID),
					(int)cursor4.getInteger(CATEGORY_ALLOWSUB),
					ownerID,
					ownerPubKey,
					cursor2.getString(UNIQUECARD_FIRSTOWNER),
					cursor2.getString(UNIQUECARD_FIRSTAUTH),
					(int) cursor2.getInteger(UNIQUECARD_AUTENTICATED)
					);
			return resp;
		}
		catch(SqlJetException e){
			e.printStackTrace();
			return null;
		}
	}

	public static GetCardJourneyResponse getCardJourney(GetCardJourneyRequest req){
		try{
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);

			ISqlJetTable tableTrades = db.getTable(TABLE_NAME_TRADES);
			ISqlJetCursor cursorTrades = tableTrades.lookup(TRADE_CARDSN_AND_CARDID, req.CardSN, req.CardUniqueID);
			ISqlJetTable tableUniqueCards = db.getTable(TABLE_NAME_UNIQUECARDS);
			ISqlJetCursor cursorCards = tableUniqueCards.lookup(UNIQUECARD_SN_AND_ID, req.CardSN, req.getCardUniqueID());


			Vector<Trade> vt = new Vector<Trade>();
			while(!cursorTrades.eof()){
				vt.add(new Trade(
						cursorTrades.getString(TRADE_OLDOWNER),
						cursorTrades.getString(TRADE_NEWOWNER),
						cursorTrades.getString(TRADE_DATE),
						(float)cursorTrades.getFloat(TRADE_LATITUDE),
						(float)cursorTrades.getFloat(TRADE_LONGITUDE)
						));
				cursorTrades.next();
			}

			return new GetCardJourneyResponse(vt, cursorCards.getString(UNIQUECARD_FIRSTOWNER), cursorCards.getString(UNIQUECARD_FIRSTAUTH), -1, -1);


		}catch(Exception e){
			e.printStackTrace();
			return new GetCardJourneyResponse("Card non-existent or internal server error");
		}
	}

	public static GetCardListByOwnerResponse getCardListByEmail(GetCardListByOwnerRequest req){
		String email= req.getOwnerEmail();
		Vector<HashMap<String,Object>> cardList= new Vector<HashMap<String,Object>>();
		try {
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);

			ISqlJetTable table1 = db.getTable(TABLE_NAME_UNIQUECARDS);
			ISqlJetCursor cursor1 = table1.lookup(UNIQUECARD_ACTUALOWNER, email);


			if (!cursor1.eof()) {
				do {
					HashMap<String,Object> aux= new HashMap<String,Object>();

					//inserisco i dati riguardanti le info uniquecard
					aux.put(UNIQUECARD_SN, cursor1.getString(UNIQUECARD_SN));
					aux.put(UNIQUECARD_ID, cursor1.getString(UNIQUECARD_ID));
					aux.put(UNIQUECARD_FIRSTAUTH, cursor1.getString(UNIQUECARD_FIRSTAUTH));
					aux.put(UNIQUECARD_FIRSTOWNER, cursor1.getString(UNIQUECARD_FIRSTOWNER));
					aux.put(UNIQUECARD_AUTENTICATED, cursor1.getInteger(UNIQUECARD_AUTENTICATED));
					aux.put(UNIQUECARD_ACTUALOWNER, cursor1.getString(UNIQUECARD_ACTUALOWNER));


					ISqlJetTable table2 = db.getTable(TABLE_NAME_CARDS);
					ISqlJetCursor cursor2 = table2.lookup(CARD_SN, cursor1.getString(UNIQUECARD_SN));
					aux.put(CARD_NAME, cursor2.getString(CARD_NAME));
					aux.put(CARD_SN, cursor2.getString(CARD_SN));
					aux.put(CARD_STATS, cursor2.getString(CARD_STATS));
					aux.put(CARD_SPECIAL, cursor2.getString(CARD_SPECIAL));
					//aux.put(CARD_IMAGE, cursor2.getBlobAsArray(CARD_IMAGE));
					aux.put(CARD_PREVIEW, cursor2.getBlobAsArray(CARD_PREVIEW));
					aux.put(CARD_CATEGORY, cursor2.getInteger(CARD_CATEGORY));
					aux.put(CARD_SUBCATEGORY, cursor2.getInteger(CARD_SUBCATEGORY));


					ISqlJetTable table3 = db.getTable(TABLE_NAME_CATEGORIES);
					ISqlJetCursor cursor3 = table3.lookup(CATEGORY_ID, cursor2.getInteger(CARD_CATEGORY));
					aux.put(CARD_CATEGORY_NAME, cursor3.getString(CATEGORY_NAME));

					ISqlJetCursor cursor4 = table3.lookup(CATEGORY_ID, cursor2.getInteger(CARD_SUBCATEGORY));
					aux.put(CARD_SUBCATEGORY_NAME, cursor4.getString(CATEGORY_NAME));

					cursor2.close();
					cursor3.close();
					cursor4.close();

					//reperimento campi public key
					String ownerID = null;
					byte[] ownerPubKey = null;
					try{
						ISqlJetTable table5 = db.getTable(TABLE_NAME_ACCOUNTS);
						ISqlJetCursor cursor5 = table5.lookup(ACCOUNT_EMAIL, cursor1.getString(UNIQUECARD_ACTUALOWNER));
						ownerID = cursor5.getString(ACCOUNT_EMAIL);
						KeyPair ownerKeyPair = SecurityUtilities.Bin2KeyPair(cursor5.getBlobAsArray(ACCOUNT_KEYPAIR));
						ownerPubKey = SecurityUtilities.SerializeToBin(ownerKeyPair.getPublic());
						//inserimento in aux TODO

						cursor5.close();
					}catch(Exception e1){
						e1.printStackTrace();
					}
					//inserimento di aux nel vector
					cardList.add(aux);

				} while(cursor1.next());
				cursor1.close();
			}

		} catch(SqlJetException e){
			e.printStackTrace();}

		return new GetCardListByOwnerResponse(cardList);
	}


	public static GetCardFromQRScanResponse getCardFromQRScan(GetCardFromQRScanRequest r){
		boolean toAuthenticate= r.getToAuthenticate();

		try{
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);

			ISqlJetTable table1 = db.getTable(TABLE_NAME_UNIQUECARDS);
			ISqlJetCursor cursor1 = table1.lookup(UNIQUECARD_SN_AND_ID, r.getCardSN(), r.getCardID());

			//autenticazione carta
			if(toAuthenticate==true ){
				if(cursor1.getInteger(UNIQUECARD_AUTENTICATED)==0 && cursor1.getString(UNIQUECARD_ACTUALOWNER)==null){
					//aggiornare il record con mail e bool authenticated, first owner
					boolean up = updateCardForAuthentication(r.getCardSN(), r.getCardID(), r.getEmail());
					if(up!=true)
						return new GetCardFromQRScanResponse("error: Authentication failed during database update");
				}

				else{
					return new GetCardFromQRScanResponse("error: the card is already authenticated");
				}
			}
			//db.close();
			//riapertura del db
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);

			ISqlJetTable table = db.getTable(TABLE_NAME_CARDS);
			ISqlJetCursor cursor = table.lookup(CARD_SN, r.getCardSN());

			ISqlJetTable table2 = db.getTable(TABLE_NAME_UNIQUECARDS);
			ISqlJetCursor cursor2 = table2.lookup(UNIQUECARD_SN_AND_ID, r.getCardSN(), r.getCardID());

			ISqlJetTable table3 = db.getTable(TABLE_NAME_CATEGORIES);
			ISqlJetCursor cursor3 = table3.lookup(CATEGORY_ID, cursor.getInteger(CARD_CATEGORY));
			ISqlJetCursor cursor4 = table3.lookup(CATEGORY_ID, cursor.getInteger(CARD_SUBCATEGORY));

			String ownerID = null;
			byte[] ownerPubKey = null;
			if(cursor2.getString(UNIQUECARD_ACTUALOWNER)!=null){
				try{
					ISqlJetTable table5 = db.getTable(TABLE_NAME_ACCOUNTS);
					ISqlJetCursor cursor5 = table5.lookup(ACCOUNT_EMAIL, cursor2.getString(UNIQUECARD_ACTUALOWNER));
					ownerID = cursor5.getString(ACCOUNT_EMAIL);
					KeyPair ownerKeyPair = SecurityUtilities.Bin2KeyPair(cursor5.getBlobAsArray(ACCOUNT_KEYPAIR));
					ownerPubKey = SecurityUtilities.SerializeToBin(ownerKeyPair.getPublic());
				}catch(Exception e1){
					e1.printStackTrace();
				}
			}

			/*
			 * this.authenticated= authenticated;
		this.firstOwner= firstOwner;
		this.firstAuth= firstAuth;
			 */
			GetCardFromQRScanResponse resp = new GetCardFromQRScanResponse(toAuthenticate,
					cursor.getString(CARD_SN), cursor2.getString(UNIQUECARD_ID),
					cursor.getString(CARD_NAME), cursor.getString(CARD_STATS),cursor.getString(CARD_SPECIAL),
					(int)cursor.getInteger(CARD_CATEGORY), (int)cursor.getInteger(CARD_SUBCATEGORY),
					cursor.getBlobAsArray(CARD_PREVIEW),
					cursor3.getString(CATEGORY_NAME), (int)cursor3.getInteger(CATEGORY_ID),
					(int)cursor3.getInteger(CATEGORY_ALLOWSUB),	cursor4.getString(CATEGORY_NAME),
					(int)cursor4.getInteger(CATEGORY_ID), (int)cursor4.getInteger(CATEGORY_ALLOWSUB),
					(int)cursor2.getInteger(UNIQUECARD_AUTENTICATED),cursor2.getString(UNIQUECARD_FIRSTOWNER),
					cursor2.getString(UNIQUECARD_FIRSTAUTH),
					ownerID,
					ownerPubKey
					);
			return resp;
		}
		catch(SqlJetException e){
			e.printStackTrace();
			return new GetCardFromQRScanResponse("Error: sql error during card query");
		}
	}


	public static GetCardImageResponse GetCardImage(GetCardImageRequest req){
		byte[] image=null;
		try {
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			ISqlJetTable table1 = db.getTable(TABLE_NAME_CARDS);
			ISqlJetCursor cursor1 = table1.lookup(CARD_SN, req.getCardSN());

			image = cursor1.getBlobAsArray(CARD_IMAGE);

			return new GetCardImageResponse(image);

		} catch (SqlJetException e) {
			e.printStackTrace();
			return new GetCardImageResponse("Error: sql error during card query");
		}


	}


	public static GetPositionsOfNearbyUsersResponse getPositionsFromRequest(GetPositionsOfNearbyUsersRequest req){
		try {
			db.beginTransaction(SqlJetTransactionMode.READ_ONLY);
			ISqlJetTable tableUsers = db.getTable(TABLE_NAME_ACCOUNTS);
			ISqlJetTable tableUnique = db.getTable(TABLE_NAME_UNIQUECARDS);

			if(req.MODE == MODE_LOCATE_SPECIFIC_USER){
				ISqlJetCursor cursorUsers = tableUsers.lookup(ACCOUNT_EMAIL, req.requestedEmail);
				String location = cursorUsers.getString(ACCOUNT_LASTLOC);
				if(location != null && !location.equals("")){
					GetPositionsOfNearbyUsersResponse resp = new GetPositionsOfNearbyUsersResponse();
					String[] split = location.split(",");
					resp.latitudes.add(Float.parseFloat(split[0]));
					resp.longitudes.add(Float.parseFloat(split[1]));
					resp.emails.add(req.requestedEmail);
					return resp;
				}
				else{
					return new GetPositionsOfNearbyUsersResponse("Error: the requested user did not activate geolocation on hist device.");
				}
			}

			if(req.MODE == MODE_LOCATE_CARD_OWNERS_NEAR_ME){
				ISqlJetCursor cursorUnique = tableUnique.lookup(UNIQUECARD_SN, req.requestedCardSN);
				GetPositionsOfNearbyUsersResponse resp = new GetPositionsOfNearbyUsersResponse();
				while(!cursorUnique.eof()){
					String owner = cursorUnique.getString(UNIQUECARD_ACTUALOWNER);
					if(owner != null && !owner.equals("")){
						ISqlJetCursor cursorUsers = tableUsers.lookup(ACCOUNT_EMAIL, owner);
						String location = cursorUsers.getString(ACCOUNT_LASTLOC);
						if(location != null && !location.equals("")){
							String[] split = location.split(",");
							float lat = Float.parseFloat(split[0]);
							float lon = Float.parseFloat(split[1]);

							if(Algorithms.distanceFrom(lat, lon, req.senderLatitude, req.senderLongitude) < 250000){
								resp.latitudes.add(lat);
								resp.longitudes.add(lon);
								resp.emails.add(owner);
							}
						}
					}

					cursorUnique.next();
				}
				return resp;
			}


			if(req.MODE == MODE_LOCATE_USERS_NEAR_ME){
				ISqlJetCursor cursorUsers = tableUsers.lookup(ACCOUNT_EMAIL, req.requestedEmail);
				GetPositionsOfNearbyUsersResponse resp = new GetPositionsOfNearbyUsersResponse();
				while(!cursorUsers.eof()){
					String useremail = cursorUsers.getString(ACCOUNT_EMAIL);
					String location = cursorUsers.getString(ACCOUNT_LASTLOC);
					if(location != null && !location.equals("")){
						String[] split = location.split(",");
						float lat = Float.parseFloat(split[0]);
						float lon = Float.parseFloat(split[1]);

						if(Algorithms.distanceFrom(lat, lon, req.senderLatitude, req.senderLongitude) < 25000){
							resp.latitudes.add(lat);
							resp.longitudes.add(lon);
							resp.emails.add(useremail);
							return resp;
						}
					}
					cursorUsers.next();
				}
				return resp;
			}

		} catch (SqlJetException e) {
			e.printStackTrace();
			return new GetPositionsOfNearbyUsersResponse("Error: sql error during query");
		}
		return null;
	}

	public static UpdateUserPositionResponse UpdateUserPosition(UpdateUserPositionRequest req){
		try {
			db.beginTransaction(SqlJetTransactionMode.WRITE);
			ISqlJetTable tableUsers = db.getTable(TABLE_NAME_ACCOUNTS);
			ISqlJetCursor cursor1 = tableUsers.lookup(ACCOUNT_EMAIL, req.email);

			if(req.password.equals(cursor1.getString(ACCOUNT_PASSWORD))){
				HashMap<String, Object> m = new HashMap<String, Object>();
				m.put(ACCOUNT_LASTLOC, req.latitude + "," + req.longitude);
				cursor1.updateByFieldNames(m);

				db.commit();

				return new UpdateUserPositionResponse();
			}
			else{
				return new UpdateUserPositionResponse("Error: wrong password, this is an hacking attempt!");
			}

		} catch (SqlJetException e) {
			e.printStackTrace();
			return new UpdateUserPositionResponse("Error: sql error during query");
		}

	}




}